#! /usr/bin/perl

use strict;
use Data::Dumper;
use File::Spec;
use FindBin;
use File::Copy;
use DBI;
use Term::ANSIColor qw(:constants);
$Term::ANSIColor::AUTORESET = 1;

main();
exit;

sub main {
    die "Usage: $0 <host> <database>\n" unless (@ARGV);
    my $host    = $ARGV[0];
    my $db_name = $ARGV[1];

    print BOLD GREEN "grant.pl start ...\n";
    print BOLD GREEN "connect database $db_name ...\n";

    my $dbh = DBI->connect( sprintf( 'dbi:Pg:host=%s;dbname=%s', $host, $db_name ),
        '', '', { AutoCommit => 1, RaiseError => 0 } );
    die "can\'t connect database.\n$DBI::errstr" unless ($dbh);

    if ($dbh) {
        print BOLD RED "   .....   OK\n";

        my %opt = (
            host    => $host,
            db_name => $db_name,
            file    => "$FindBin::Bin/_grant.sql"
        );
        unlink( $opt{file} );

        # table / view
        my @tables = $dbh->tables( undef, "public", "", "", { noprefix => 1, pg_noprefix => 1 } );
#        print Dumper(\@tables);
        for my $table (@tables) {
                &revoke( $table, \%opt );
                &grant( $table, \%opt );
#            }
        }

        # sequence
        my $sth = $dbh->prepare("SELECT relname FROM pg_catalog.pg_statio_user_sequences;");
        $sth->execute();
        while(my ($relname) = $sth->fetchrow_array) {
            &revoke( $relname, \%opt );
            &grant( $relname, \%opt );
        }
        
        # grant
        my $cmd = sprintf( 'psql -h %s %s < %s', $opt{host}, $opt{db_name}, $opt{file} );
        `$cmd`;
        print BOLD GREEN "finish to grant on tables/views\n";

        File::Copy::copy( $opt{file}, $opt{file} . '.log' );
        print BOLD GREEN "log file is $opt{file}.log\n";
    }
}

sub grant {
    my $table = shift;
    my $opt   = shift;

    my $grant = sprintf( "GRANT ALL ON table %s TO PUBLIC;\n", $table );
    &write_to_file( $grant, $opt );
}

sub revoke {
    my $table = shift;
    my $opt   = shift;

    my $revoke = sprintf( "REVOKE ALL ON %s FROM PUBLIC;\n", $table );
    &write_to_file( $revoke, $opt );
}

sub write_to_file {
    my $string = shift;
    my $opt    = shift;

    open FH, ">>$opt->{file}" or die "can't open file: $opt->{file}";
    print FH $string;
    close FH;
}
